﻿/*
' DotNetNuke® - http://www.dotnetnuke.com
' Copyright (c) 2002-2009
' by DotNetNuke Corporation
'
' Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
' documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
' the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
' to permit persons to whom the Software is furnished to do so, subject to the following conditions:
'
' The above copyright notice and this permission notice shall be included in all copies or substantial portions 
' of the Software.
'
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
' TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
' THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
' CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
' DEALINGS IN THE SOFTWARE.
*/

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using System.Text.RegularExpressions;

namespace ZSafeProjectNameZ.Tests
{
    public static class DatabaseManager
    {
        #region Public Methods
        public static List<int> GetMockModules()
        {
            List<int> modules = new List<int>();
            string sproc = "{databaseOwner}{objectQualifier}Modules_GetModules".Replace("{objectQualifier}", DatabaseEnvironment.ObjectQualifier).Replace("{databaseOwner}", DatabaseEnvironment.DatabaseOwner);
            using (IDataReader reader = SqlHelper.ExecuteReader(DatabaseEnvironment.ConnectionString, CommandType.StoredProcedure, sproc))
            {
                while (reader.Read())
                    modules.Add((int)reader["ModuleID"]);
            }
            return modules;
        }

        public static List<int> GetMockUserIds()
        {
            List<int> users = new List<int>();
            string sproc = "{databaseOwner}{objectQualifier}Users_GetUsers".Replace("{objectQualifier}", DatabaseEnvironment.ObjectQualifier).Replace("{databaseOwner}", DatabaseEnvironment.DatabaseOwner);
            using (IDataReader reader = SqlHelper.ExecuteReader(DatabaseEnvironment.ConnectionString, CommandType.StoredProcedure, sproc))
            {
                while (reader.Read())
                    users.Add((int)reader["UserID"]);
            }
            return users;
        }
        #endregion

        #region Private Methods
        public static void ReApplyScripts()
        {
            string script;
            script = File.ReadAllText(Path.Combine(DatabaseEnvironment.SourceDatabaseFolderPath, DatabaseEnvironment.TestDatabaseSetupScript));
            RunScript(script);
            foreach (string scriptPath in DatabaseEnvironment.ModuleInstallScripts)
            {
                script = File.ReadAllText(Path.Combine(DatabaseEnvironment.SourceDatabaseFolderPath, scriptPath));
                RunScript(script);
            }
        }

        private static void RunScript(string script)
        {
            script = script.Replace("{objectQualifier}", DatabaseEnvironment.ObjectQualifier).Replace("{databaseOwner}", DatabaseEnvironment.DatabaseOwner);

            string[] scripts = SqlDelimiterRegex.Split(script);
            foreach (string item in scripts)
            {
                using (SqlConnection conn = new SqlConnection(DatabaseEnvironment.ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(item, conn);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }
        #endregion

        #region Private Fields
        // Following line adapted from the DotNetNuke.Data.SqlDataProvider SqlDelimiterRegex property
        private static Regex SqlDelimiterRegex = new Regex(@"(?<=(?:[^\w]+|^))GO(?=(?: |\t)*?(?:\r?\n|$))", RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Multiline);
        #endregion

    }

    // CLEAN:
    //public static class DatabaseManager
    //{
    //    #region Private Methods

    //    private static void AddLogin(string login, string password)
    //    {
    //        // Connect to the SQL Server
    //        Server server = new Server(TestEnvironment.DatabaseServer);

    //        //Get the Login
    //        Login dbLogin = server.Logins[login];
    //        if (dbLogin == null)
    //        {
    //            //Create a new Login
    //            dbLogin = new Login(server, login);
    //            dbLogin.LoginType = LoginType.SqlLogin;
    //            dbLogin.PasswordPolicyEnforced = false;

    //            dbLogin.Create(password, LoginCreateOptions.None);
    //        }
    //    }

    //    private static string CopyDatabase(TestContext context, string sourceDatabasePath, string databaseName)
    //    {
    //        // Get the fileName (create the folder if neccessary)
    //        string databasePath = GetDataBaseFileName(context, databaseName, ".mdf");
    //        sourceDatabasePath = String.Format("{0}.mdf", Path.Combine(sourceDatabasePath, databaseName));

    //        // Copy the database to the test database directory
    //        File.Copy(sourceDatabasePath, databasePath);
    //        return databasePath;
    //    }

    //    private static void DropDatabase(Server server, string databaseName)
    //    {
    //        // Drop the database
    //        Database db = server.Databases[databaseName];
    //        if (db != null)
    //        {
    //            server.KillDatabase(databaseName);
    //            while (server.Databases[databaseName] != null)
    //            {
    //                Thread.Sleep(100);
    //            }
    //        }
    //    }

    //    #endregion

    //    #region Public Methods

    //    public static void AddUser(string databaseName, string login, string password, string[] roles)
    //    {
    //        // Connect to the SQL Server
    //        Server server = new Server(TestEnvironment.DatabaseServer);

    //        //Get Database
    //        Database db = server.Databases[databaseName];

    //        //Make sure Login exists
    //        AddLogin(login, password);

    //        //Create a User for the Login
    //        User dbUser = new User(db, login);
    //        dbUser.Login = login;
    //        dbUser.Create();
    //        foreach (string role in roles)
    //        {
    //            dbUser.AddToRole(role);
    //        }
    //    }

    //    public static void AttachDatabase(string databaseName, string databaseFile)
    //    {
    //        // Connect to the SQL Server
    //        // ServerConnection connection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);
    //        Server server = new Server(TestEnvironment.DatabaseServer);


    //        // Attach the database
    //        server.AttachDatabase(databaseName, new StringCollection()
    //        {
    //            databaseFile
    //        });
    //        while (server.Databases[databaseName].State != SqlSmoState.Existing)
    //        {
    //            Thread.Sleep(100);
    //        }

    //    }

    //    public static void SetDatabaseOwner(string DatabaseName, string ownerLogin)
    //    {
    //        //Connect to the server
    //        Server server = new Server(TestEnvironment.DatabaseServer);

    //        //Locate the Database
    //        Database db = server.Databases[DatabaseName];

    //        //Change the owner of the DB
    //        db.SetOwner(ownerLogin, true);
    //    }

    //    public static void CopyAndAttachDatabase(TestContext context, string sourceDatabasePath, string databaseName)
    //    {
    //        string destinationPath = CopyDatabase(context, sourceDatabasePath, databaseName);

    //        // Attach the copied database to SQL Server
    //        AttachDatabase(databaseName, destinationPath);
    //    }

    //    public static void CreateDatabase(TestContext context, string databaseName)
    //    {
    //        // Connect to the SQL Server
    //        //ServerConnection connection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);
    //        Server server = new Server(TestEnvironment.DatabaseServer);

    //        // Drop the database
    //        DropDatabase(server, databaseName);

    //        //Instantiate a database object
    //        Database db = new Database(server, databaseName);

    //        //Create a PRIMARY FileGroup
    //        FileGroup fileGroup = new FileGroup(db, "PRIMARY");
    //        db.FileGroups.Add(fileGroup);

    //        //Create Primary Data File
    //        DataFile dbFile = new DataFile(fileGroup, databaseName, GetDataBaseFileName(context, databaseName, ".mdf"));
    //        dbFile.Size = (double)(5.0 * 1024.0);
    //        dbFile.GrowthType = FileGrowthType.Percent;
    //        dbFile.Growth = 25.0;
    //        dbFile.IsPrimaryFile = true;
    //        fileGroup.Files.Add(dbFile);

    //        //Create Log File
    //        string logFileName = String.Format("{0}_Log", databaseName);
    //        LogFile logFile = new LogFile(db, logFileName, GetDataBaseFileName(context, logFileName, ".ldf"));
    //        logFile.Size = (double)(5.0 * 1024.0);
    //        logFile.GrowthType = FileGrowthType.Percent;
    //        logFile.Growth = 25.0;
    //        db.LogFiles.Add(logFile);

    //        //Create Database
    //        db.Create(false);
    //    }

    //    public static void DetachAndCopyDatabase(string databaseName, string destinationPath, string destinationFileName)
    //    {
    //        // Connect to the SQL Server
    //        // ServerConnection connection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);
    //        Server server = new Server(TestEnvironment.DatabaseServer);

    //        //Get the database object
    //        Database db = server.Databases[databaseName];

    //        if (db != null)
    //        {
    //            //Get the file group
    //            FileGroup fileGroup = db.FileGroups["PRIMARY"];

    //            //Get the file
    //            DataFile dbFile = fileGroup.Files[0];
    //            string fileName = dbFile.FileName;

    //            //Detach Database
    //            server.KillAllProcesses(db.Name);
    //            db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
    //            db.Alter(TerminationClause.RollbackTransactionsImmediately);
    //            server.DetachDatabase(db.Name, true);

    //            //Copy file(s) to destination
    //            if (!Directory.Exists(destinationPath))
    //            {
    //                Directory.CreateDirectory(destinationPath);
    //            }
    //            string destinationDatabasePath = Path.Combine(destinationPath, destinationFileName) + ".mdf";
    //            File.Copy(fileName, destinationDatabasePath, true);
    //        }
    //    }

    //    public static void DropDatabase(string databaseName)
    //    {
    //        // Connect to the SQL Server
    //        //ServerConnection connection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);       
    //        Server server = new Server(TestEnvironment.DatabaseServer);

    //        // Drop the database
    //        DropDatabase(server, databaseName);
    //    }

    //    public static string GetDataBaseFileName(TestContext context, string databaseName, string extension)
    //    {
    //        string testDatabaseDirectory = Path.GetFullPath(String.Format(@"{0}\{1}",
    //                                                            TestEnvironment.DatabasePath,
    //                                                            context.Test.FullName));
    //        if (!Directory.Exists(testDatabaseDirectory))
    //        {
    //            Directory.CreateDirectory(testDatabaseDirectory);
    //        }

    //        string destinationRoot = Path.Combine(testDatabaseDirectory, databaseName);
    //        string databasePath = String.Concat(destinationRoot, extension);

    //        return databasePath;
    //    }

    //    public static void ExecuteSQLUpdateStringOnDatabase(string databaseName, string dbUserName, string dbUserPassword, string sqlString)
    //    {
    //        //Create the connection string
    //        string connectionString = String.Format("Server={0};Database={1};uid={2};pwd={3};",
    //                                                TestEnvironment.DatabaseServer, databaseName, dbUserName, dbUserPassword);
    //        //Create connection object and open the connection
    //        SqlConnection connection = new SqlConnection(connectionString);
    //        connection.Open();

    //        //Execute the SQL statement
    //        SqlCommand command = new SqlCommand(sqlString, connection);
    //        int rowsAffected = command.ExecuteNonQuery();

    //        //Close the connection
    //        connection.Close();
    //        if (rowsAffected == 0)
    //        {
    //            throw new System.Exception("No items in the Database were updated");
    //        }

    //    }



    //    #endregion


    //}
}
